Namespace persistent
    Public Class batchJobRequest


        Public Shared Function RequestInsert(ByVal username As String, ByVal reqid As String, ByVal jobid As String) As Long
            Dim strSQL As String
            strSQL = " insert into batch_req values('" & reqid & "','" & jobid & "',sysdate,'0','" & username & "') "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function RequestInsertAsFinished(ByVal username As String, ByVal reqid As String, ByVal jobid As String) As Long
            Dim strSQL As String
            strSQL = " insert into batch_req values('" & reqid & "','" & jobid & "',sysdate,'1','" & username & "') "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function RequestParameterInsert(ByVal reqid As String, ByVal parameterNo As Integer, ByVal val As String) As Long
            Dim strSQL As String
            strSQL = " insert into batch_req_param values('" & reqid & "'," & parameterNo & ",'" & val & "') "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function RequestFinish(ByVal reqid As String, ByVal status As String) As Long
            Dim strSQL As String
            strSQL = " update batch_req set status='" & status & "' where req_id ='" & reqid & "' "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function SelectRequest(ByVal job As String) As DataTable
            Dim strSQL As String
            strSQL = " select * from batch_req where job_id='" & job & "' and status in ('0','4') and username is not null "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function SelectRequestParameter(ByVal reqid As String) As DataTable
            Dim strSQL As String
            strSQL = " select * from batch_req_param where req_id ='" & reqid & "' order by parameterno "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function hareketList(ByVal username As String, ByVal jobid As String) As DataTable
            Dim strSQL As String
            strSQL = " select br.req_id,br.tarih,(select ack from batch_req_status where status=br.status) status,br.status statusno  from batch_req br where username='" & username & "' and job_id='" & jobid & "' "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function logWrite(ByVal req_id As String, ByVal str As String) As Long
            Dim strSQL As String
            strSQL = " insert into batch_req_log values('" & req_id & "',sysdate, '" & str & "')"
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function logRead(ByVal reqid As String) As DataTable
            Dim strSQL As String
            strSQL = " select log from batch_req_log where req_id='" & reqid & "' "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function RequestParameter(ByVal reqid As String, ByVal username As String) As DataTable
            Dim strSQL As String
            strSQL = " select r.TARIH tarih,j.ACK parname,p.VALUE parvalue , s.ACK statu "
            strSQL = strSQL + " from batch_req r,batch_req_param p,BATCH_JOBS_PARAMETER j,batch_req_status s "
            strSQL = strSQL + " where r.req_id='" & reqid & "' and r.username='" & username & "' "
            strSQL = strSQL + " and r.req_id=p.req_id "
            strSQL = strSQL + " and p.parameterno=j.parameter_no "
            strSQL = strSQL + " and r.JOB_ID=j.JOB_ID "
            strSQL = strSQL + " and r.STATUS=s.STATUS "
            strSQL = strSQL + " order by j.PARAMETER_NO "
            Return myDBConn.myORATable(strSQL)
        End Function


        Public Shared Function select_customers_for_sms1() As DataTable
            Dim strSQL As String
            'strSQL = " select customer_id,(select co_id from contract_all where customer_id=c.customer_id) from customer_all c  where custcode like '3.220.10%' and cstype='a' and cslevel=40 and tmcode=467 "
            'strSQL = strSQL + " and custcode>'3.220.10.00.100119' and custcode<'3.220.10.00.100599' "

            strSQL = " select customer_id from customer_all c  where custcode like '3.%' and cstype='a' and cslevel=40 and rownum<2001"

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function select_customers_for_sms2() As DataTable
            Dim strSQL As String
            'strSQL = " select customer_id from customer_all where custcode like '3.220.10%' and cstype='a' and cslevel=40 and tmcode=467 "
            'strSQL = strSQL + " and custcode>'3.220.10.00.103309' and custcode<'3.220.10.00.105311' "
            'strSQL = " select customer_id from customer_all where custcode like '3.182.10%' and cstype='a' and cslevel=40 and tmcode=23 "
            strSQL = " select customer_id from customer_all c  where custcode like '4.%' and cstype='a' and cslevel=40 and rownum<2001"
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function select_customers_for_sms3() As DataTable
            Dim strSQL As String
            strSQL = " select customer_id from customer_all where custcode like '3.220.10%' and cstype='a' and cslevel=40 and tmcode=467 "
            strSQL = strSQL + " and custcode>'3.220.10.00.105311' and custcode<'3.220.10.00.106000' "

            Return myDBConn.myORATable(strSQL)
        End Function

    End Class

End Namespace
